﻿--月度考勤汇总表
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_monthlyAttendanceSummary_query')
          and type in ('P','PC'))
   drop procedure bd_proc_monthlyAttendanceSummary_query
go

CREATE PROCEDURE bd_proc_monthlyAttendanceSummary_query
  @year VARCHAR(MAX)
 ,@month VARCHAR(MAX)
 ,@yearMonth VARCHAR(MAX)
 ,@users VARCHAR(MAX)
 ,@deps VARCHAR(MAX)
 ,@serchTxt VARCHAR(MAX)
 ,@userCompany VARCHAR(MAX)
 ,@userSnyLx VARCHAR(MAX)
 ,@userSnyType VARCHAR(MAX)
 ,@flag INT = 0
AS
BEGIN
  SET NOCOUNT ON;

  --SET @users = '20001247,20001253,20001255,20001274,20001276,20001280,20001283,20001331,20001333,20001344,20001345,20001359,20001360,20001368,20001377,20001378,20001390,20001395,20001404,20001405,20001413,20001449,20001458,20001461,20001473,20001476,20001481,20001511,20001528,20001533,20001534,20001536,20001541,20001547,20001551,20001579,20001586,20001596,20001604,20001621,20001636,20001646,20001653,20001658,20001663,20001665,20001683,20001694,20001704,20001733,20001743,20001752,20001758,20001760,20001761,20001772,20001787,20001792,20001794,20001802,20001805,20001816,20001821,20001849,20001852,20001860,20001876,20001879,20001880,20001894,20001895,20001910,20001911,20001922,20001939,20001946,20001952,20001961,20001968,20001980,20001983,20001984,20001992,20001998,20002006,20002013,20002026,20002046,20002055,20002059,20002062,20002068,20002090,20002091,20002095,20002096,20002100,20002109,20002113,20002122,20002124,20002133,20002139,20002140,20002142,20002151,20002159,20002166,20002169,20002171,20002175,20002193,20002196,20002199,20002217,20002233,20002234,20002241,20002248,20002250,20002271,20002276,20002312,20002321,20002332,20002355,20002356,20002361,20002368,20002370,20002376,20002382,20002388,20002413,20002417,20002425,20002431,20002439,20002451,20002487,20002495,20002515,20002521,20002523,20002528,20002552,20002553,20002554,20002555,20002556,20002557,20002559,20002584,20002612,20002615,20002633,20002638,20002670,20002700,20002763,20002780,20002782,20002783,20002787,20002794,20002814,20002871,20002898,20002912,20002914,20002944,20002962,20002964,20002969,20002991,20003033,20003036,20003037,20003039,20003045,20003057,20003092,20003097,20003108,20003111,20003114,20003115,20003123,20003139,20003145,20003148,20003164,20003166,20003182,20003288,20003292,20003299,20003331,20003383,20003386'
--目前正确的人73
  --SET @users = '20001276,20001283,20001333,20001359,20001360,20001405,20001413,20001481,20001536,20001541,20001551,20001586,20001621,20001636,20001663,20001665,20001683,20001694,20001704,20001743,20001760,20001772,20001794,20001802,20001849,20001852,20001879,20001880,20001939,20001984,20002006,20002013,20002055,20002059,20002062,20002090,20002091,20002096,20002100,20002139,20002142,20002151,20002169,20002171,20002193,20002196,20002217,20002234,20002248,20002250,20002276,20002312,20002332,20002361,20002368,20002382,20002388,20002451,20002552,20002555,20002556,20002559,20002612,20002912,20002914,20002944,20002962,20002964,20002991,20003033,20003036,20003039,20003292'
  --SET @users = '20001255,20001761,20002113,20002356,20002633'

		--创建查询表
		create table #temp_monthlyAttendanceSummary_data(
		user_serial bigint,--人员ID
		dep_serial bigint,--部门ID
		depName varchar(50),--部门
		division varchar(50),--科室
	  userNo varchar(50),--工号
	  userName varchar(50),--姓名
	  userCompany varchar(50),--所属公司
		userSnyLx varchar(50),--员工类型
		userSnyType varchar(50),--工作状态
	  workDate varchar(50),--上班日期
	  cardNumber varchar(50),--饭卡号
	  overtimeMeals varchar(50)  DEFAULT('0'),--加班就餐（天）
	  numberOfDaysDue varchar(50)  DEFAULT('0'),--应出勤天数
	  actualDaysOfAttendance varchar(50)  DEFAULT('0'),--实际出勤天数
		--班次
		anEarlyClass varchar(50)  DEFAULT('0'),--早早班
		morningShift varchar(50)  DEFAULT('0'),--早班
		middleClass varchar(50)  DEFAULT('0'),--中班
		sunInEightOut varchar(50)  DEFAULT('0'),--日8进8出
		nightClass varchar(50) DEFAULT('0'),--夜班
		nightInEightOut varchar(50)  DEFAULT('0'),--夜8进8出
		midMorning varchar(50)  DEFAULT('0'),--早中
		middlePeriod varchar(50)  DEFAULT('0'),--中夜
		--请假（缺勤）小时
	  annualLeave DECIMAL(18,2)  DEFAULT('0'),--年休假
	  maritalLeave DECIMAL(18,2)  DEFAULT('0'),--婚假
	  funeralLeave DECIMAL(18,2)  DEFAULT('0'),--丧假
	  sickLeave DECIMAL(18,2)  DEFAULT('0'),--病假
	  skillLeave DECIMAL(18,2)  DEFAULT('0'),--工伤假
	  prenatalCheckUpLeave DECIMAL(18,2)  DEFAULT('0'),--产检假
	  preNatalLeave DECIMAL(18,2)  DEFAULT('0'),--产前假（怀孕7个月每天1小时）
		preNatalLeave2 DECIMAL(18,2)  DEFAULT('0'),--产前假
		maternityLeave DECIMAL(18,2)  DEFAULT('0'),--产假
		breastfeedingLeave DECIMAL(18,2)  DEFAULT('0'),--哺乳假
		lactatingFalse DECIMAL(18,2)  DEFAULT('0'),--授乳假（1周岁内每天1小时）
		familyPlanningLeave DECIMAL(18,2)  DEFAULT('0'),--计划生育假
		paternityLeave DECIMAL(18,2)  DEFAULT('0'),--陪产假
		stayed DECIMAL(18,2)  DEFAULT('0'),--公出
		other DECIMAL(18,2) DEFAULT('0'),--其他
	  --
	  beLate varchar(50)  DEFAULT('0'),--迟到
	  leaveEarly varchar(50)  DEFAULT('0'),--早退
		absenteeism varchar(50)  DEFAULT('0'),--旷工
		--截止上月未结算
		historyPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		historyWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		historyOweWork DECIMAL(18,2)  DEFAULT('0'),--欠工
		--当月实际
		currentMonthPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		currentMonthWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		currentMonthHolidayOvertime DECIMAL(18,2)  DEFAULT('0'),--节假日加班
		currentMonthPutBan DECIMAL(18,2)  DEFAULT('0'),--放班
		currentMonthRest DECIMAL(18,2)  DEFAULT('0'),--调休
		currentPersonalLeave DECIMAL(18,2)  DEFAULT('0'),--事假
		currentOweWork DECIMAL(18,2)  DEFAULT('0'),--欠工
		--当月抵扣后
		cMDeductionPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		cMDeductionWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--截止上月抵扣后结余
		hDeductionPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		hDeductionWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--结算当月加班工资
		cMCOPPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		cMCOPWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--转存工
		archivedWorkPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		archivedWorkWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--结算截止上月加班工资
		hCOPPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		hCOPWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--截止上月结余
		hSurplusPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		hSurplusWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		--截止本月结余
		finalSurplusPeacetimeOvertime DECIMAL(18,2)  DEFAULT('0'),--平时加班
		finalSurplusWeekendOvertime DECIMAL(18,2)  DEFAULT('0'),--双休加班
		finalSurplusOweWork DECIMAL(18,2)  DEFAULT('0')--欠工
	)

  --添加#temp_monthlyAttendanceSummary_data表数据
	INSERT INTO #temp_monthlyAttendanceSummary_data(user_serial,dep_serial,division,userNo,userName,userCompany,userSnyLx,userSnyType,workDate,cardNumber)
	select
		a.user_serial,
		b.dep_serial,
		b.dep_name,
		a.user_no,
		a.user_lname,
		a.user_company,
		a.user_sny_lx,
		a.user_sny_type,
		convert(varchar(10),a.user_workday,120),
		a.card_number
	from dt_user a
  left join dt_dep b on a.user_dep = b.dep_serial;

	--修改加班就餐（天）	应出勤天数	实际出勤天数
	update a set a.overtimeMeals = c.overtimeMeals,a.numberOfDaysDue = c.numberOfDaysDue, a.actualDaysOfAttendance = c.actualDaysOfAttendance
	from #temp_monthlyAttendanceSummary_data a
	left join (select user_serial,card_year,card_month,cast(isnull(sum(should_hours_calc/60.0/8.0),0) as DECIMAL(18,2)) as numberOfDaysDue,cast(isnull(sum(actual_hours_calc/60.0/8.0),0) as DECIMAL(18,2)) as actualDaysOfAttendance,isnull(sum(ot_allowance),0) as overtimeMeals from bd_kq_timecard where
	card_year = @year and card_month = @month
	GROUP BY user_serial,card_year,card_month) c on a.user_serial = c.user_serial;

	--修改班次
		--早早班
		update a set
			a.anEarlyClass = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50000' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--早班
		update a set
			a.morningShift = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50001' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--中班
		update a set
			a.middleClass = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50002' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--日8进8出
		update a set
			a.sunInEightOut = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50003' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--夜班
		update a set
			a.nightClass = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50004' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--夜8进8出
		update a set
			a.nightInEightOut = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50005' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--早中
		update a set
			a.midMorning = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50006' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

		--中夜
		update a set
			a.middlePeriod = isnull(d.actualDaysOfAttendance,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				a.user_serial,
				isnull(sum(case when shift_id>0 then 1 else 0 end),0) as actualDaysOfAttendance
			from bd_kq_timecard a
			inner join zt_banci b on a.shift_id = b.id
			where a.card_year = @year and a.card_month = @month and b.shift_type = '50007' and a.work_status = 0
			GROUP BY a.user_serial
		) d on a.user_serial = d.user_serial;

  --修改请假（缺勤）小时
		--年休假 annualLeave
		update a set
			a.annualLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10001'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--婚假 maritalLeave
		update a set
			a.maritalLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10008'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--丧假 funeralLeave
		update a set
			a.funeralLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10009'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

	  --病假 sickLeave
		update a set
			a.sickLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10005'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--工伤假 skillLeave
		update a set
			a.skillLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10011'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--产检假 prenatalCheckUpLeave
		update a set
			a.prenatalCheckUpLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10014'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--产前假（怀孕7个月每天1小时 preNatalLeave
		update a set
			a.preNatalLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10013'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--产前假 preNatalLeave2
		update a set
			a.preNatalLeave2 = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10012'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--产假 maternityLeave
		update a set
			a.maternityLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10015'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--哺乳假 breastfeedingLeave
		update a set
			a.breastfeedingLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10016'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--授乳假（1周岁内每天1小时）lactatingFalse
		update a set
			a.lactatingFalse = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10018'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--计划生育假	familyPlanningLeave
		update a set
			a.familyPlanningLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10019'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--陪产假	paternityLeave
		update a set
			a.paternityLeave = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10017'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--公出 stayed
		update a set
			a.stayed = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10007'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

		--其他 other
		update a set
			a.other = isnull(d.duration,0)
		from #temp_monthlyAttendanceSummary_data a
		left join (
			select
				user_serial,
				convert(varchar(7),kssj,120) as yearMonth,
				bh,
				cast(sum(jsjg/60.0) as DECIMAL(18,2)) as duration
			from lr_kqingj
			where whether_validity = 1 and convert(varchar(7),kssj,120) = @yearMonth and bh = '10999'
			GROUP BY user_serial,convert(varchar(7),kssj,120),bh
		) d on a.user_serial = d.user_serial;

	--修改迟到	早退	旷工
  update a set
		a.beLate = case when e.status = 4 then e.count else 0 end,
		a.leaveEarly = case when e.status = 5 then e.count else 0 end,
		a.absenteeism = case when e.status = 6 then e.count else 0 end
	from #temp_monthlyAttendanceSummary_data a
	left join (
		select
			user_serial,
			card_year,
			card_month,
			status,
			count(1) as count
		from bd_kq_timecard
	where
	card_year = @year and card_month = @month
	GROUP BY user_serial,card_year,card_month,status
	) e on a.user_serial = e.user_serial;

	--修改
-- 		截止上月未结算
-- 		当月实际
-- 		当月抵扣后
-- 		截止上月抵扣后结余
-- 		结算当月加班工资
-- 		转存工
-- 		结算截止上月加班工资
-- 		截止上月结余
-- 		截止本月结余
	update a set
		a.historyPeacetimeOvertime = f.historyPeacetimeOvertime,
		a.historyWeekendOvertime = f.historyWeekendOvertime,
		a.historyOweWork = f.historyOweWork,
		a.currentMonthPeacetimeOvertime = f.currentMonthPeacetimeOvertime,
		a.currentMonthWeekendOvertime = f.currentMonthWeekendOvertime,
		a.currentMonthHolidayOvertime = f.currentMonthHolidayOvertime,
		a.currentMonthPutBan = f.currentMonthPutBan,
		a.currentMonthRest = f.currentMonthRest,
		a.currentPersonalLeave = f.currentPersonalLeave,
		a.currentOweWork = f.currentOweWork,
		a.cMDeductionPeacetimeOvertime = f.cMDeductionPeacetimeOvertime,
		a.cMDeductionWeekendOvertime = f.cMDeductionWeekendOvertime,
		a.hDeductionPeacetimeOvertime = f.hDeductionPeacetimeOvertime,
		a.hDeductionWeekendOvertime = f.hDeductionWeekendOvertime,
		a.cMCOPPeacetimeOvertime = f.cMCOPPeacetimeOvertime,
		a.cMCOPWeekendOvertime = f.cMCOPWeekendOvertime,
		a.archivedWorkPeacetimeOvertime = f.archivedWorkPeacetimeOvertime,
		a.archivedWorkWeekendOvertime = f.archivedWorkWeekendOvertime,
		a.hCOPPeacetimeOvertime = f.hCOPPeacetimeOvertime,
		a.hCOPWeekendOvertime = f.hCOPWeekendOvertime,
		a.hSurplusPeacetimeOvertime = f.hSurplusPeacetimeOvertime,
		a.hSurplusWeekendOvertime = f.hSurplusWeekendOvertime,
		a.finalSurplusPeacetimeOvertime = f.finalSurplusPeacetimeOvertime,
		a.finalSurplusWeekendOvertime = f.finalSurplusWeekendOvertime,
		a.finalSurplusOweWork = f.finalSurplusOweWork
	from #temp_monthlyAttendanceSummary_data a
  left join (
	select
		user_serial,
		cast(isnull(prev_month_ordinary/60.0,0) as DECIMAL(18,2)) as historyPeacetimeOvertime,
		cast(isnull(prev_month_weekend/60.0,0) as DECIMAL(18,2)) as historyWeekendOvertime,
		cast(isnull(prev_month_owe/60.0,0) as DECIMAL(18,2)) as historyOweWork,
		cast(isnull(cur_month_ordinary/60.0,0) as DECIMAL(18,2)) as currentMonthPeacetimeOvertime,
		cast(isnull(cur_month_weekend/60.0,0) as DECIMAL(18,2)) as currentMonthWeekendOvertime,
		cast(isnull(cur_month_statutory/60.0,0) as DECIMAL(18,2)) as currentMonthHolidayOvertime,
		cast(isnull(cur_month_fangban/60.0,0) as DECIMAL(18,2)) as currentMonthPutBan,
		cast(isnull(cur_month_rest/60.0,0) as DECIMAL(18,2)) as currentMonthRest,
		cast(isnull(cur_month_leave/60.0,0) as DECIMAL(18,2)) as currentPersonalLeave,
		cast(isnull(cur_month_owe/60.0,0) as DECIMAL(18,2)) as currentOweWork,
		cast(isnull(cur_month_deduction_ordinary/60.0,0) as DECIMAL(18,2)) as cMDeductionPeacetimeOvertime,
		cast(isnull(cur_month_deduction_weekend/60.0,0) as DECIMAL(18,2)) as cMDeductionWeekendOvertime,
		cast(isnull(prev_month_deduction_ordinary/60.0,0) as DECIMAL(18,2)) as hDeductionPeacetimeOvertime,
		cast(isnull(prev_month_deduction_weekend/60.0,0) as DECIMAL(18,2)) as hDeductionWeekendOvertime,
		cast(isnull(cur_month_settle_ordinary/60.0,0) as DECIMAL(18,2)) as cMCOPPeacetimeOvertime,
		cast(isnull(cur_month_settle_weekend/60.0,0) as DECIMAL(18,2)) as cMCOPWeekendOvertime,
		cast(isnull(cur_month_save_ordinary/60.0,0) as DECIMAL(18,2)) as archivedWorkPeacetimeOvertime,
		cast(isnull(cur_month_save_weekend/60.0,0) as DECIMAL(18,2)) as archivedWorkWeekendOvertime,
		cast(isnull(prev_month_settle_ordinary/60.0,0) as DECIMAL(18,2)) as hCOPPeacetimeOvertime,
		cast(isnull(prev_month_settle_weekend/60.0,0) as DECIMAL(18,2)) as hCOPWeekendOvertime,
		cast(isnull(prev_month_save_ordinary/60.0,0) as DECIMAL(18,2)) as hSurplusPeacetimeOvertime,
		cast(isnull(prev_month_save_weekend/60.0,0) as DECIMAL(18,2)) as hSurplusWeekendOvertime,
		cast(isnull(cur_month_result_ordinary/60.0,0) as DECIMAL(18,2)) as finalSurplusPeacetimeOvertime,
		cast(isnull(cur_month_result_weekend/60.0,0) as DECIMAL(18,2)) as finalSurplusWeekendOvertime,
		cast(isnull(prev_month_result_owe/60.0,0) as DECIMAL(18,2)) as finalSurplusOweWork
	from bd_kq_ot_settle where settle_year = @year and settle_month = @month) f on a.user_serial = f.user_serial


	DECLARE @sql varchar(max);
	set @sql = '';

	if(@users!='' or @users!=null)
		BEGIN
			set @sql +=' and user_serial in('+@users+')';
		end;

	if(@deps!='' or @deps!=null)
		BEGIN
			set @sql +=' and dep_serial in('+@deps+')';
		end;

	if(@serchTxt!='' or @serchTxt!=null)
		BEGIN
			set @sql +=' and (userNo like ''%'+@serchTxt+'%'' or userName like ''%'+@serchTxt+'%'')';
		end;

	if(@userCompany!='' or @userCompany!=null)
		BEGIN
			set @sql +=' and userCompany = '''+@userCompany+'''';
		end;

  if(@userSnyLx!='' or @userSnyLx!=null)
		BEGIN
			set @sql +=' and userSnyLx = '''+@userSnyLx+'''';
		end;

  if(@userSnyType!='' or @userSnyType!=null)
		BEGIN
			set @sql +=' and userSnyType = '''+@userSnyType+'''';
		end;

	DECLARE @sql2 varchar(max);

	set @sql2 = 'select * from #temp_monthlyAttendanceSummary_data where 1=1 '+@sql+' ORDER BY userNo';

	exec(@sql2);
	
	--  清除临时表
 	drop table #temp_monthlyAttendanceSummary_data;
  SET NOCOUNT OFF;
END